package db;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBConnector {
	private static final String DRIVER_NAME = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	private static final String CONNECTION_STRING = "jdbc:sqlserver://localhost:1433;databaseName=test;user=sa;password=123456";
	private Connection con;
	private Statement state;
	private PreparedStatement pre;
	private CallableStatement call;

	private static DBConnector instance;

	private DBConnector() {
		try {
			loadDriver(DRIVER_NAME);
			System.out.println("load driver successfully");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}

	}

	public static DBConnector getInstance() {
		if (instance == null) {
			instance = new DBConnector();
		}
		return instance;
	}

	// load driver
	private void loadDriver(String driver) throws ClassNotFoundException {
		Class.forName(driver);
	}

	// get connection
	// create statement & execute
	public Statement createStatement() throws SQLException {
		con = DriverManager.getConnection(CONNECTION_STRING);
		System.out.println("create statement successfully");
		state = con.createStatement();
		return state;
	}

	private PreparedStatement preparedStatement(String sql) throws SQLException {
		con = DriverManager.getConnection(CONNECTION_STRING);
		System.out.println("create statement successfully");
		pre = con.prepareStatement(sql);
		return pre;
	}

	private CallableStatement callStatement(String sql) throws SQLException {
		con = DriverManager.getConnection(CONNECTION_STRING);
		System.out.println("create statement successfully");
		call = con.prepareCall(sql);
		return call;
	}

	// process result

	public ResultSet execQuery(String query) throws SQLException {
		createStatement();
		return state.executeQuery(query);
	}

	// update
	public int execUpadate(String updateQuery) throws SQLException {
		createStatement();
		return state.executeUpdate(updateQuery);
	}

	public void closeConnection() {
		try {
			if (con != null)
				con.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}
